package com.itheima.dubbo.mysql.mapper;

import com.itheima.commons.model.domain.lastYear;
import com.itheima.commons.model.domain.thisYear;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;


public interface CountInfoMapper {
    /**
     * 累计用户
     *
     * @return
     */
    @Select("SELECT COUNT(1) FROM `tb_user`")
    Integer cumulativeUsers();

    /**
     * 过去30天活跃用户
     *
     * @param start 开始时间
     * @param end   结束时间
     * @return
     */
    @Select("select count(1) FROM\n" +
            "(SELECT user_id ,COUNT(1) FROM tb_user_login_record where logtime >#{start} and logtime<#{end} GROUP BY user_id) as a")
    Integer activePassMonth(String start, String end);

    /**
     * 过去7天活跃用户
     *
     * @param start 开始时间
     * @param end   结束时间
     * @return
     */
    @Select("select count(1) FROM\n" +
            "(SELECT user_id ,COUNT(1) FROM tb_user_login_record where logtime >#{start} and logtime<#{end} GROUP BY user_id) as a")
    Integer activePassWeek(String start, String end);

    /**
     * 今日新增用户
     *
     * @param start
     * @param end
     * @return
     */
    @Select("SELECT Count(1) FROM tb_user where created > #{start} and created < #{end}")
    Integer newUsersToday(Date start, Date end);

    /**
     * 今日登录次数
     *
     * @param start
     * @param end
     * @return
     */
    @Select("SELECT COUNT(1) FROM tb_user_login_record where logtime > #{start} and logtime< #{end}")
    Integer loginTimesToday(Date start, Date end);

    /**
     * 今日活跃用户
     *
     * @param start
     * @param end
     * @return
     */
    @Select("select count(1) FROM\n" +
            "(SELECT user_id ,COUNT(1) FROM tb_user_login_record where logtime >#{start} and logtime <#{end} GROUP BY user_id) as a")
    Integer activeUsersToday(Date start, Date end);

    /**
     * 昨日活跃用户
     *
     * @param start
     * @param end
     * @return
     */
    @Select("select count(1) FROM\n" +
            "(SELECT user_id ,COUNT(1) FROM tb_user_login_record where logtime >#{start} and logtime <#{end} GROUP BY user_id) as a")
    Integer activeUsersYesterday(Date start, Date end);

    /**
     *  查询一段时间内的新增用户(thisYear)
     * @param startDate
     * @param endDate
     */
    @Select("select DATE_FORMAT(created,'%e号') title ,Count(*) amount FROM tb_user where created >#{startDate} and created <#{endDate} GROUP BY DATE_FORMAT(created,'%m-%e') ")
    List<thisYear> queryEveryNewlyThisYear(String startDate, String endDate);

    /**
     *  查询一段时间内的新增用户(lastYear)
     * @param startDate
     * @param endDate
     */
    @Select("select DATE_FORMAT(created,'%e号') title ,Count(*) amount FROM tb_user where created >#{startDate} and created <#{endDate} GROUP BY DATE_FORMAT(created,'%m-%e') ")
    List<lastYear> queryEveryNewlyLastYear( String startDate, String endDate);


    /**
     * 查询一段时间内的活跃用户 按天分组(返回的结果是今年的对象)
     * @param startDate
     * @param endDate
     * @return
     */
    @Select("select DATE_FORMAT(logtime,'%m月%e日') title,Count(*) amount FROM tb_user_login_record where logtime >#{startDate}and logtime <#{endDate} GROUP BY DATE_FORMAT(logtime,'%Y-%m-%e')")
    List<thisYear> queryEveryActiveThisYear(String startDate, String endDate);


    /**
     * 查询一段时间内的活跃用户 按天分组(返回的结果是去年的对象)
     * @param startDate
     * @param endDate
     * @return
     */
    @Select("select DATE_FORMAT(logtime,'%m月%e日') title,Count(*) amount FROM tb_user_login_record where logtime >#{startDate}and logtime <#{endDate} GROUP BY DATE_FORMAT(logtime,'%Y-%m-%e')")
    List<lastYear> queryEveryActiveLastYear(String startDate, String endDate);
    /**
     * 查询昨天注册新增 次日登录的用户数
     * @param todayStart 今天开始时间
     * @param todayEnd 今天结束时间
     * @param yesterdayStart 昨天开始时间
     * @param yesterdayEnd  昨天结束时间
     * @return
     */
    @Select("select count(1) from\n" +
            "(select tu.*,tulr.logtime,tulr.place,tulr.equipment\n" +
            "from tb_user tu,tb_user_login_record tulr WHERE tu.id = tulr.user_id and created > #{yesterdayStart} and created <#{yesterdayEnd} and logtime >#{todayStart}and logtime <#{todayEnd}) as a;")
    Integer queryActiveWithYesterdayNewly(Date todayStart,Date todayEnd,Date yesterdayStart,Date yesterdayEnd);

    /**
     * 查询新增数量
     * @param start 开始时间
     * @param end   结束时间
     * @return
     */
    @Select("select count(1) from tb_user where created > #{start}  and created <#{end}")
    Integer queryAddCount(String start, String end);

    /**
     * 查询活跃用户数量
     * @param start
     * @param end
     * @return
     */
    @Select("select count(1) from\n" +
            "(SELECT * FROM `tb_user_login_record` where logtime > #{start} and logtime < #{end} GROUP BY user_id)as a")
    Integer queryActiveCount(String start, String end);
}

